package com.chuangdun.flutter.plugin.alarm

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteException
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log

/**
 * 闹钟数据库助手类
 */
class AlarmDatabaseHelper(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "alarm_database.db"
        private const val DATABASE_VERSION = 1

        // 表名
        private const val TABLE_ALARMS = "alarms"

        // 列名
        private const val COLUMN_ID = "id"
        private const val COLUMN_ALARM_ID = "alarm_id"
        private const val COLUMN_TAG = "tag"
        private const val COLUMN_DATE = "date"
        private const val COLUMN_HOUR = "hour"
        private const val COLUMN_MINUTE = "minute"
        private const val COLUMN_TITLE = "title"
        private const val COLUMN_BODY = "body"
        private const val COLUMN_REPEATING = "repeating"

        // 创建表的SQL语句
        private const val CREATE_TABLE_ALARMS = """
            CREATE TABLE $TABLE_ALARMS (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_ALARM_ID TEXT NOT NULL,
                $COLUMN_TAG TEXT NOT NULL,
                $COLUMN_DATE TEXT DEFAULT NULL,
                $COLUMN_HOUR INTEGER NOT NULL,
                $COLUMN_MINUTE INTEGER NOT NULL,
                $COLUMN_TITLE TEXT NOT NULL,
                $COLUMN_BODY TEXT NOT NULL,
                $COLUMN_REPEATING INTEGER NOT NULL DEFAULT 0
            )
        """
    }

    override fun onCreate(db: SQLiteDatabase) {
        try {
            db.execSQL(CREATE_TABLE_ALARMS)
            Log.d(TAG, "数据库表创建成功: $TABLE_ALARMS")
        } catch (e: SQLiteException) {
            Log.e(TAG, "创建数据库表失败: $TABLE_ALARMS", e)
        }
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        try {
            db.execSQL("DROP TABLE IF EXISTS $TABLE_ALARMS")
            onCreate(db)
            Log.i(TAG, "数据库已升级: v$oldVersion -> v$newVersion")
        } catch (e: SQLiteException) {
            Log.e(TAG, "升级数据库失败: v$oldVersion -> v$newVersion", e)
        }
    }

    /**
     * 添加闹钟
     */
    fun addAlarm(alarmModel: AlarmModel): Long {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_ALARM_ID, alarmModel.alarmId)
            put(COLUMN_TAG, alarmModel.tag)
            put(COLUMN_DATE, alarmModel.date)
            put(COLUMN_HOUR, alarmModel.hour)
            put(COLUMN_MINUTE, alarmModel.minute)
            put(COLUMN_TITLE, alarmModel.title)
            put(COLUMN_BODY, alarmModel.body)
            put(COLUMN_REPEATING, if (alarmModel.repeating) 1 else 0)
        }
        return try {
            val id = db.insertOrThrow(TABLE_ALARMS, null, values)
            Log.d(TAG, "闹钟添加成功: ID=${alarmModel.id}, AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}")
            id
        } catch (e: SQLiteException) {
            Log.e(TAG, "添加闹钟失败: AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}", e)
            -1
        } finally {
            db.close()
        }
    }

    /**
     * 更新闹钟
     */
    fun updateAlarm(alarmModel: AlarmModel): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_ALARM_ID, alarmModel.alarmId)
            put(COLUMN_TAG, alarmModel.tag)
            put(COLUMN_DATE, alarmModel.date)
            put(COLUMN_HOUR, alarmModel.hour)
            put(COLUMN_MINUTE, alarmModel.minute)
            put(COLUMN_TITLE, alarmModel.title)
            put(COLUMN_BODY, alarmModel.body)
            put(COLUMN_REPEATING, if (alarmModel.repeating) 1 else 0)
        }

        return try {
            val rowsAffected = db.update(
                TABLE_ALARMS,
                values,
                "$COLUMN_ID = ?",
                arrayOf(alarmModel.id.toString())
            )
            val success = rowsAffected > 0
            if (success) {
                Log.d(TAG, "闹钟更新成功: ID=${alarmModel.id}, AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}")
            } else {
                Log.w(TAG, "闹钟更新失败，未找到记录: ID=${alarmModel.id}")
            }
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "更新闹钟失败: ID=${alarmModel.id}, AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 根据alarm_id,和tag更新闹钟.
     */
    fun updateAlarmByAlarmId(alarmModel: AlarmModel): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_ALARM_ID, alarmModel.alarmId)
            put(COLUMN_TAG, alarmModel.tag)
            put(COLUMN_DATE, alarmModel.date)
            put(COLUMN_HOUR, alarmModel.hour)
            put(COLUMN_MINUTE, alarmModel.minute)
            put(COLUMN_TITLE, alarmModel.title)
            put(COLUMN_BODY, alarmModel.body)
            put(COLUMN_REPEATING, if (alarmModel.repeating) 1 else 0)
        }

        return try {
            val rowsAffected = db.update(
                TABLE_ALARMS,
                values,
                "$COLUMN_ALARM_ID = ? AND $COLUMN_TAG = ?",
                arrayOf(alarmModel.alarmId, alarmModel.tag)
            )
            val success = rowsAffected > 0
            if (success) {
                Log.d(TAG, "闹钟更新成功: AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}")
            } else {
                Log.w(TAG, "闹钟更新失败，未找到记录: AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}")
            }
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据AlarmID更新闹钟失败: AlarmID=${alarmModel.alarmId}, Tag=${alarmModel.tag}", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 根据主键ID删除闹钟
     */
    fun deleteAlarm(id: Int): Boolean {
        val db = this.writableDatabase
        return try {
            val rowsAffected = db.delete(
                TABLE_ALARMS,
                "$COLUMN_ID = ?",
                arrayOf(id.toString())
            )
            val success = rowsAffected > 0
            if (success) {
                Log.d(TAG, "闹钟删除成功: ID=$id")
            } else {
                Log.w(TAG, "闹钟删除失败，未找到记录: ID=$id")
            }
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "删除闹钟失败: ID=$id", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 根据alarm_id,和tag删除闹钟.
     */
    fun deleteAlarmByAlarmId(alarmId: String, tag: String): Boolean {
        val db = this.writableDatabase
        return try {
            val rowsAffected = db.delete(
                TABLE_ALARMS,
                "$COLUMN_ALARM_ID = ? AND $COLUMN_TAG = ?",
                arrayOf(alarmId, tag)
            )
            val success = rowsAffected > 0
            if (success) {
                Log.d(TAG, "闹钟删除成功: AlarmID=$alarmId, Tag=$tag")
            } else {
                Log.w(TAG, "闹钟删除失败，未找到记录: AlarmID=$alarmId, Tag=$tag")
            }
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据AlarmID删除闹钟失败: AlarmID=$alarmId, Tag=$tag", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 根据tag删除闹钟
     */
    fun deleteAlarmsByTag(tag: String): Boolean {
        val db = this.writableDatabase
        return try {
            val rowsAffected = db.delete(
                TABLE_ALARMS,
                "$COLUMN_TAG = ?",
                arrayOf(tag)
            )
            val success = rowsAffected > 0
            if (success) {
                Log.d(TAG, "闹钟批量删除成功: Tag=$tag, Count=$rowsAffected")
            } else {
                Log.w(TAG, "闹钟批量删除失败，未找到记录: Tag=$tag")
            }
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据Tag批量删除闹钟失败: Tag=$tag", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 获取所有闹钟
     */
    fun getAllAlarms(): List<AlarmModel> {
        val alarmsList = mutableListOf<AlarmModel>()
        val db = this.readableDatabase
        var cursor: Cursor? = null
        try {
            cursor = db.query(TABLE_ALARMS, null, null, null, null, null, null)
            if (cursor.moveToFirst()) {
                do {
                    val alarmModel = cursorToAlarmModel(cursor)
                    alarmsList.add(alarmModel)
                } while (cursor.moveToNext())
            }
            Log.d(TAG, "查询所有闹钟成功: Count=${alarmsList.size}")
        } catch (e: SQLiteException) {
            Log.e(TAG, "查询所有闹钟失败", e)
        } finally {
            cursor?.close()
            db.close()
        }
        return alarmsList
    }

    /**
     * 根据tag获取所有闹钟
     */
    fun getAlarmsByTag(tag: String): List<AlarmModel> {
        val alarmsList = mutableListOf<AlarmModel>()
        val db = this.readableDatabase
        var cursor: Cursor? = null
        try {
            cursor = db.query(TABLE_ALARMS, null, "$COLUMN_TAG = ?", arrayOf(tag), null, null, null)
            if (cursor.moveToFirst()) {
                do {
                    val alarmModel = cursorToAlarmModel(cursor)
                    alarmsList.add(alarmModel)
                } while (cursor.moveToNext())
            }
            Log.d(TAG, "根据Tag查询闹钟成功: Tag=$tag, Count=${alarmsList.size}")
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据Tag查询闹钟失败: Tag=$tag", e)
        } finally {
            cursor?.close()
            db.close()
        }
        return alarmsList
    }

    /**
     * 根据主键ID获取单个闹钟
     */
    fun getAlarmById(id: Int): AlarmModel? {
        val db = this.readableDatabase
        var cursor: Cursor? = null
        var alarmModel: AlarmModel? = null
        try {
            cursor = db.query(
                TABLE_ALARMS,
                null,
                "$COLUMN_ID = ?",
                arrayOf(id.toString()),
                null,
                null,
                null
            )

            if (cursor.moveToFirst()) {
                alarmModel = cursorToAlarmModel(cursor)
                Log.d(TAG, "根据ID查询闹钟成功: ID=$id")
            } else {
                Log.d(TAG, "根据ID未找到闹钟: ID=$id")
            }
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据ID查询闹钟失败: ID=$id", e)
        } finally {
            cursor?.close()
            db.close()
        }
        return alarmModel
    }

    /**
     * 根据alarm_id获取单个闹钟
     */
    fun getAlarmByAlarmId(alarmId: String, tag: String): AlarmModel? {
        val db = this.readableDatabase
        var cursor: Cursor? = null
        var alarmModel: AlarmModel? = null
        try {
            cursor = db.query(
                TABLE_ALARMS,
                null,
                "$COLUMN_ALARM_ID = ? and $COLUMN_TAG = ?",
                arrayOf(alarmId, tag),
                null,
                null,
                null
            )

            if (cursor.moveToFirst()) {
                alarmModel = cursorToAlarmModel(cursor)
                Log.d(TAG, "根据AlarmID和Tag查询闹钟成功: AlarmID=$alarmId, Tag=$tag")
            } else {
                Log.d(TAG, "根据AlarmID和Tag未找到闹钟: AlarmID=$alarmId, Tag=$tag")
            }
        } catch (e: SQLiteException) {
            Log.e(TAG, "根据AlarmID和Tag查询闹钟失败: AlarmID=$alarmId, Tag=$tag", e)
        } finally {
            cursor?.close()
            db.close()
        }
        return alarmModel
    }

    /**
     * 删除所有闹钟
     */
    fun deleteAllAlarms(): Boolean {
        val db = this.writableDatabase
        return try {
            val rowsAffected = db.delete(TABLE_ALARMS, null, null)
            val success = true // delete always returns number of rows deleted, even 0 is success for "delete all"
            Log.d(TAG, "所有闹钟已删除: Count=$rowsAffected")
            success
        } catch (e: SQLiteException) {
            Log.e(TAG, "删除所有闹钟失败", e)
            false
        } finally {
            db.close()
        }
    }

    /**
     * 将Cursor转换为AlarmModel
     */
    private fun cursorToAlarmModel(cursor: Cursor): AlarmModel {
        return AlarmModel(
            id = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_ID)),
            alarmId = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ALARM_ID)),
            tag = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TAG)),
            date = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DATE)),
            hour = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_HOUR)),
            minute = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_MINUTE)),
            title = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TITLE)),
            body = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BODY)),
            repeating = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_REPEATING)) == 1
        )
    }
}